# -*- coding: utf-8 -*-
# @Time: 2024/10/24 20:34
# @Author: foxhuty
# @File: new_datasource.py
# -*- coding: utf-8 -*-
import pandas as pd
from sympy import symbols, solve


class DataSource:
    A_T_range = [86, 100]
    B_T_range = [71, 85]
    C_T_range = [56, 70]
    D_T_range = [41, 55]
    E_T_range = [30, 40]

    def __init__(self, file):
        self.file = file

    def get_data(self):
        excel_file = pd.ExcelFile(self.file)
        return [pd.read_excel(excel_file, sheet_name) for sheet_name in excel_file.sheet_names]

    def get_grade_data(self):
        data = self.get_data()[1]
        subjects = [col for col in data.columns if col in ['政治', '地理', '生物', '化学']]
        for subject in subjects:
            data = self.get_grade(data, subject)
        data.to_excel(self.file + '等级.xlsx', index=False)
        print('successfully done')

    def get_grade(self, data, subject):
        max_score, min_score = self.get_subject_max_min_score(data, subject)
        data[subject + '等级'] = data[subject].apply(lambda x: self.get_level(x, min_score, max_score))

        data[subject + '赋值'] = data[subject].apply(lambda x: self.get_added_score(x, min_score, max_score))
        return data


@staticmethod
def get_subject_max_min_score(data, subject):
    subject_num = data[subject].count()
    A_num = int(subject_num * 0.15)
    B_num = int(subject_num * 0.35)
    C_num = int(subject_num * 0.35)
    D_num = int(subject_num * 0.13)
    E_num = subject_num - A_num - B_num - C_num - D_num
    data = data.sort_values(by=subject, ascending=False).reset_index(drop=True)

    min_score = [data[subject].iloc[A_num - 1], data[subject].iloc[A_num + B_num - 1],
                 data[subject].iloc[A_num + B_num + C_num - 1], data[subject].iloc[A_num + B_num + C_num + D_num - 1],
                 data[subject].iloc[-1]]
    max_score = [data[subject].iloc[A_num], data[subject].iloc[A_num + B_num],
                 data[subject].iloc[A_num + B_num + C_num], data[subject].iloc[A_num + B_num + C_num + D_num],
                 data[subject].iloc[-1]]
    return max_score, min_score


@staticmethod
def get_level(score, min_score):
    if score >= min_score[0]:
        return 'A'
    elif score >= min_score[1]:
        return 'B'
    elif score >= min_score[2]:
        return 'C'
    elif score >= min_score[3]:
        return 'D'
    elif score < min_score[3]:
        return 'E'


@staticmethod
def get_added_score(y, y1, y2, t1, t2):
    t = symbols('t')
    if y == y1[0]:
        return t1[0]
    return round(solve((t2 - t) / (t - t1) - (y2 - y) / (y - y1), t)[0])


if __name__ == '__main__':
    file_path = 'D:/data_test/高2026级学生10月考成绩汇总.xlsx'  # 使用斜线代替反斜杠以避免转义
    datasource = DataSource(file_path)
    datasource.get_grade_data()
